﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using AkazoNobel.Scheduler.StoreMasterData.Common;
using AkazoNobel.Scheduler.StoreMasterData.DataAccess.Model;
using Dapper;

namespace AkazoNobel.Scheduler.StoreMasterData.DataAccess.Service
{
    public static class TintingService
    {
        public static List<Tinting> GetList()
        {
            List<Tinting> tintings;
            
            //1.先从EApprove数据库中抓取最近一周的审核数据 (tinting数据库没有权限建视图，建在mdc库中)
            using (var connTinting = SqlConnectionHelper.GetConnection(Constant.MDC_DB_NAME))
            {
                tintings = connTinting.Query<Tinting>("SELECT * FROM v_EApproveTasks", null).ToList();
            }

            //2.与MDC数据库中比对，这条审核数据有没有抓取过
            var results = new List<Tinting>();
            using (var connMdc = SqlConnectionHelper.GetConnection(Constant.MDC_DB_NAME))
            {
                foreach (var tinting in tintings)
                {
                    var count = connMdc.Query<int>(
                        "SELECT COUNT(ID) FROM t_StoreMasterData WHERE SerialNumber=@SerialNumber",
                        new { SerialNumber = tinting.SerialNumber }).Single();

                    if (count == 0)
                    {
                        if (!string.IsNullOrEmpty(tinting.CertiCode))
                        {
                            tinting.CertiCode = tinting.CertiCode.PadLeft(8, '0');
                        }
                        results.Add(tinting);
                    }
                }
            }

            return results;
        }

        public static IEnumerable<TintingDevice> GetLastUpdateDeviceList()
        {
            List<TintingDevice> devices;

            //从Tinting数据库中抓取一个小时之内更新的数据
            using (var connTinting = SqlConnectionHelper.GetConnection(Constant.TINTING_DB_NAME))
            {
                devices = connTinting.Query<TintingDevice>("SELECT * FROM Devices WHERE DATEDIFF(hh, LastUpdateTime, getdate())=0", null).ToList();
            }

            return devices;
        }

        public static TintingDevice GetTintingDeviceByFullsetsn(string fullsetsn,string type)
        {
            
            using (var connTinting = SqlConnectionHelper.GetConnection(Constant.TINTING_DB_NAME))
            {
                var device = connTinting.Query<TintingDevice>("SELECT * FROM Devices where FixedAsset = @fullsetsn and type = @type", new{fullsetsn,type}).FirstOrDefault();
                return device??new TintingDevice();
            }
        }

    }
}
